SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[usp_AddNewSpeciality]
    (
      @fdCollID VARCHAR(50),
      @fdSpecID VARCHAR(50),
      @fdSpecName NVARCHAR(50),
      @fdSpecType INT,
      @fdSpecIntroduction NTEXT,
      @fdSpecCampus VARCHAR(50),
      @fdSpecAddID VARCHAR(50),
      @fdSpecBachelorNumber INT,
      @fdSpecMasterNumber INT,
      @fdSpecDoctorNumber INT
    )
AS 
    DECLARE @Count INT
    SET @Count = (
                   SELECT   COUNT(fdSpecID)
                   FROM     dbo.FS_Speciality
                   WHERE    fdSpecID = @fdSpecID
                 )
    IF @Count = 0 
        BEGIN
            INSERT  INTO dbo.FS_Speciality
                    (
                      fdSpecID,
                      fdSpecName,
                      fdCollID,
                      fdSpecType
                    )
            VALUES  (
                      @fdSpecID, -- fdSpecID - varchar(50)
                      @fdSpecName, -- fdSpecName - nvarchar(50)
                      @fdCollID, -- fdCollID - varchar(50)
                      @fdSpecType  -- fdSpecType - int
                    )
            INSERT  INTO dbo.JOL_SpecialityIntroduction
                    (
                      fdSpecID,
                      fdSpecCampus,
                      fdSpecIntroduction,
                      fdSpecAddTime,
                      fdSpecAddID,
                      fdSpecVerifyStatus,
                      fdSpecVerifyTime,
                      fdSpecVerifyID,
                      fdSpecBachelorNumber,
                      fdSpecMasterNumber,
                      fdSpecDoctorNumber
                    )
            VALUES  (
                      @fdSpecID, -- fdSpecID - varchar(50)
                      @fdSpecCampus, -- fdSpecCampus - varchar(50)
                      @fdSpecIntroduction, -- fdSpecIntroduction - ntext
                      GETDATE(), -- fdSpecAddTime - datetime
                      @fdSpecAddID, -- fdSpecAddID - varchar(50)
                      0, -- fdSpecVerifyStatus - bit
                      NULL, -- fdSpecVerifyTime - datetime
                      NULL, -- fdSpecVerifyID - varchar(50)
                      @fdSpecBachelorNumber, -- fdSpecBachelorNumber - int
                      @fdSpecMasterNumber, -- fdSpecMasterNumber - int
                      @fdSpecDoctorNumber  -- fdSpecDoctorNumber - int
                    )
        END
    ELSE 
        SELECT  '已存在相同编号的专业，不能再添加'
GO
